using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace AxeFrog.Data.MSSQL
{
	[ExportConnectionProvider("MSSQL")]
	public class MSSQLConnectionProvider : IConnectionProvider
	{
		public IDbConnection RequestConnection(string connectionString)
		{
			var conn = new SqlConnection(connectionString);
			try
			{
				conn.Open();
			}
			catch(Exception ex)
			{
#if DEBUG
				if(System.Diagnostics.Debugger.IsAttached)
				{
					System.Diagnostics.Debug.WriteLine("Failed to open a connection: " + ex.Message);
					System.Diagnostics.Debugger.Break();
				}
				else
					throw new Exception("Failed to open a connection: " + ex.Message);
#else
				throw new Exception("Failed to open a connection: " + ex.Message);
#endif
			}
			return conn;
		}

		public string AppendIdentitySelectorTo(string sqlInsertStatement)
		{
			return string.Concat(sqlInsertStatement, ";", Environment.NewLine, "SELECT SCOPE_IDENTITY();");
		}

		public string ModifySqlToSetRecordLimit(SqlSelectStatement sqlSelect, int nextStringFormatIndex)
		{
			return sqlSelect.Compile(new SqlSelectStatementModifications {
				AfterSelect = "TOP {" + nextStringFormatIndex + "}"
			});
		}

		public string ModifySqlToSetRecordLimitAndStartIndex(SqlSelectStatement sqlSelect, int nextStringFormatIndex)
		{
			StringBuilder sb = new StringBuilder("WITH [ResultSet] AS (SELECT");
			sb.Append(string.IsNullOrEmpty(sqlSelect.SelectFields) ? "*" : sqlSelect.SelectFields)
				.Append(string.Concat(", ROW_NUMBER() OVER (ORDER BY ", sqlSelect.OrderBy, ") AS [__RowNum]"));
			if(!string.IsNullOrEmpty(sqlSelect.From))
				sb.Append(" FROM ").Append(sqlSelect.From);
			if(!string.IsNullOrEmpty(sqlSelect.Where))
				sb.Append(" WHERE ").Append(sqlSelect.Where);
			sb.Append(") SELECT * FROM [ResultSet] WHERE [__RowNum] BETWEEN {")
				.Append(nextStringFormatIndex)
				.Append("} AND {")
				.Append(nextStringFormatIndex+1)
				.Append("} ORDER BY [__RowNum];");
			return sb.ToString();
		}
	}
}